insert overwrite table jms_dm.dm_cusc_demand_sign_network_summary_dt partition (dt)
select sign_early_scantime      -- 第一次签收时间
     , sign_early_scansitecode  -- 第一次签收网点code
     , sign_early_scansite_name -- 第一次签收网点名称
     , sign_agent_name          --签收网点对应的代理区code
     , sign_agent_code          --签收网点对应的代理区名称
     , sign_fran_code           --签收网点对应的加盟商code
     , sign_fran_name           --签收网点对应的加盟商名称
     , sign_provider_id         --签收网点对应的省id
     , sign_provider_desc       --签收网点对应的省名称
     , sign_city_id             --签收网点对应的城市id
     , sign_city_desc           --签收网点对应的城市名称
     , sum(is_need_call_num)                         as need_call_num
     , sum(is_have_call_num)                         as have_call_num
     , sum(is_have_call_num) / sum(is_need_call_num) as call_rate
     , sum(is_gt6_seconds)                           as gt6_seconds
     , sum(is_gt6_seconds) / sum(is_need_call_num)   as gt6_seconds_rate
     , sum(max_is_performance)                           as performance_bill_num
     , sum(max_is_performance) / sum(is_need_call_num)   as performance_bill_rate
    , '{{ execution_date | cst_ds }}' as dt
from jms_dm.dm_cusc_demand_sign_taking_waybill_detail_dt
where dt = '{{ execution_date | cst_ds }}'
and sign_early_scantime is not null
and sign_early_scansitecode is not null
group by sign_early_scantime               -- 运单号
       , sign_early_scansitecode  -- 第一次签收网点code
       , sign_early_scansite_name -- 第一次签收网点名称
       , sign_agent_name          --签收网点对应的代理区code
       , sign_agent_code          --签收网点对应的代理区名称
       , sign_fran_code           --签收网点对应的加盟商code
       , sign_fran_name           --签收网点对应的加盟商名称
       , sign_provider_id         --签收网点对应的省id
       , sign_provider_desc       --签收网点对应的省名称
       , sign_city_id             --签收网点对应的城市id
       , sign_city_desc --签收网点对应的城市名称
DISTRIBUTE BY dt
;